use fieldbook 
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Message')
BEGIN 
 DROP TABLE Message
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Household')
BEGIN 
 DROP TABLE Household
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Field')
BEGIN 
 DROP TABLE Field
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppUser')
BEGIN 
 DROP TABLE AppUser
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Role')
BEGIN 
 DROP TABLE Role
END
GO 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MeasureUnit')
BEGIN 
 DROP TABLE MeasureUnit
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MeasureCategory')
BEGIN 
 DROP TABLE MeasureCategory
END
go

/* Create table Role */
CREATE TABLE dbo.Role
	(
	RoleID int NOT NULL IDENTITY (1, 1),
	Description varchar(20) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Role ADD CONSTRAINT
	PK_Role PRIMARY KEY CLUSTERED 
	(
	RoleID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/* Create table AppUser */
CREATE TABLE dbo.AppUser
	(
	UserID int NOT NULL IDENTITY (1, 1),
	RoleID int NOT NULL,
	UserName varchar(20) NULL,
	Password varchar(20) NULL,
	JMBG varchar(15) NOT NULL,
	FirstName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	StreetName nvarchar(50) NOT NULL,
	StreetNumber varchar(5) NOT NULL, 
	PostalCode nvarchar(10) NOT NULL,
	City nvarchar(50) NOT NULL,
	EmailAdress varchar(50) NULL,
	PhoneNo varchar(50) NULL,
	Birthdate datetime NULL,
	Status int NOT NULL DEFAULT (0)
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.AppUser ADD CONSTRAINT
	PK_AppUser PRIMARY KEY CLUSTERED 
	(
	UserID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.AppUser ADD CONSTRAINT
	FK_AppUser_Role FOREIGN KEY
	(
	RoleID
	) REFERENCES dbo.Role
	(
	RoleID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Measure category */
CREATE TABLE dbo.MeasureCategory
	(
	CategoryID int NOT NULL IDENTITY (1, 1),
	Description varchar(20) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.MeasureCategory ADD CONSTRAINT
	PK_MeasureCategory PRIMARY KEY CLUSTERED 
	(
	CategoryID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/* Create table MeasureUnit */
CREATE TABLE dbo.MeasureUnit
	(
	MeasureUnitID int NOT NULL IDENTITY (1, 1),
	CategoryID int NOT NULL,
	Description varchar(20) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.MeasureUnit ADD CONSTRAINT
	PK_MeasureUnitr PRIMARY KEY CLUSTERED 
	(
	MeasureUnitID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.MeasureUnit ADD CONSTRAINT
	FK_MeasureUnit_MeasureCategory FOREIGN KEY
	(
	CategoryID
	) REFERENCES dbo.MeasureCategory
	(
	CategoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table field */ 
CREATE TABLE dbo.Field
	(
	FieldID bigint NOT NULL IDENTITY (1, 1),
	Cataster varchar(20) NOT NULL,
	OwnerID int NULL,
	LeaseeID int NULL,
	MeasureUnitID int NOT NULL, 
	Area decimal (19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	PK_Field PRIMARY KEY CLUSTERED 
	(
	FieldID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_Owner FOREIGN KEY
	(
	OwnerID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_Leaseee FOREIGN KEY
	(
	LeaseeID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_MeasureUnit FOREIGN KEY
	(
	MeasureUnitID
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table household */
CREATE TABLE dbo.Household
	(
	HeadID int NOT NULL,
	MemberID int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	PK_Household PRIMARY KEY CLUSTERED 
	(
	HeadID ASC, MemberID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	FK_Household_Head FOREIGN KEY
	(
	HeadID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	FK_Household_Member FOREIGN KEY
	(
	MemberID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Message */
CREATE TABLE dbo.Message
	(
	MessageID bigint IDENTITY(1,1) NOT NULL,
	SenderID int NULL,
	ReceiverID int NOT NULL,
	Caption nvarchar (255),
	Content ntext,
	MessageTypeID tinyint NOT NULL,
	MessagestatusID tinyint NOT NULL,
	MessageReadStatus bit NOT NULL DEFAULT(0)
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	PK_Message PRIMARY KEY CLUSTERED 
	(
	MessageID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	FK_Message_Sender FOREIGN KEY
	(
	SenderID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	FK_Message_Receiver FOREIGN KEY
	(
	ReceiverID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
